package com.neo.util;

import com.neo.entity.Mytest;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
//import org.apache.poi.ss.usermodel.Row;

/**
 * 吧数据读取到list中
 * Created by n on 2015/4/29.
 */
public class InsertInfoToExcelFormla {



    //该方法处理excel的数据，把第一列标记为id（递增），第9列标记为结果（默认是PASS），第10列标记为姓名
    public static List<Mytest> setExcelData(String fileName,String sheetname) throws Exception {
      String uuid=  UUID.randomUUID().toString();
        List<Mytest> list=new ArrayList<>();
        InputStream in = new FileInputStream(fileName);    //创建输入流
        XSSFWorkbook hw = new XSSFWorkbook(new FileInputStream(fileName
                //D:\WPS
               ));
        XSSFSheet hsheet = hw.getSheet(sheetname);;// 获取文件的指定工作表m 默认的第一个Row row = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int totalRows = hsheet.getPhysicalNumberOfRows();    // 总行数
        int totalCells = hsheet.getPhysicalNumberOfRows();//总列数，根据第一行得来的
        System.out.println("列数:" + totalCells + " 行数：" + totalRows);

        //依次获取每一行
        for (int i = 1; i <= hsheet.getLastRowNum(); i++) {
            XSSFRow row = (XSSFRow) hsheet.getRow(i);// 获取行对象
            int rowNum = 0;
            try {
                rowNum = row.getRowNum();
            } catch (Exception e) {
               continue;
            }



            int numberOfCells = row.getLastCellNum();//获取当前行的列数
            for (int c = 0; c < numberOfCells; c++) { // 总列(格)
            String lieming=    CellReference.convertNumToColString(c);

                Mytest mytest=new Mytest();
                mytest.setUuid(uuid);
                XSSFCell cell = row.getCell(c);
                if (cell == null) {
                    continue;
                } else {
                    int cellType = cell.getCellType();
                    mytest.setPosition(lieming+""+(i+1));
                    mytest.setComputer(cell.toString().replace("后评价输入界面!","@@"));
                }
                list.add(mytest);
            }
        }
        //写入数据，关闭
        System.out.println(list);
        in.close();
        return list;
    }

}

